package com.edu.dao.impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.Orders;
import com.edu.bean.User;
import com.edu.dao.BaseDao;
import com.edu.dao.UserDao;

/**
 * 用户数据访问层实现
 */
public class UserDaoImpl implements UserDao {
   
    
	@Override
	public int selectUsers(User user) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM user";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public List<User> getAllUsers(int pageNum, int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<User> users = new ArrayList();
		String sql = "SELECT * FROM user LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				User user = new User();
				user.setUserId(res.getInt("userId"));
	            user.setUsername(res.getString("username"));
	            user.setPassword(res.getString("password"));
	            user.setSex(res.getString("sex"));
	            user.setRealname(res.getString("realname"));
	            user.setEmail(res.getString("email"));
	            user.setTelephone(res.getString("telephone"));
	            user.setIdNum(res.getString("idNum"));
	            user.setHeadimg(res.getString("headimg"));
				users.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		return users;
	}

	@Override
	public User getUserById(int userId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		User user=null;
		String sql = "SELECT * FROM user WHERE userId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,userId);
			res = ps.executeQuery();
			if(res.next()) {
				user = new User();
				user.setUserId(res.getInt("userId"));
	            user.setUsername(res.getString("username"));
	            user.setPassword(res.getString("password"));
	            user.setSex(res.getString("sex"));
	            user.setRealname(res.getString("realname"));
	            user.setEmail(res.getString("email"));
	            user.setTelephone(res.getString("telephone"));
	            user.setIdNum(res.getString("idNum"));
	            user.setHeadimg(res.getString("headimg"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return user;
	}

	@Override
	public int addUser(User user) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql="";
			if(user.getHeadimg()==null) {
				sql = "INSERT INTO user(username,password,sex,realName,email,telephone,idNum) VALUES (?,?,?,?,?,?,?)";
			}else {
				sql = "INSERT INTO user(username,password,sex,realName,email,telephone,idNum,headImg) VALUES (?,?,?,?,?,?,?,?)";
			}
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getSex());
			ps.setString(4, user.getRealname());
			ps.setString(5, user.getEmail());
			ps.setString(6,user.getTelephone());
			ps.setString(7, user.getIdNum());
			if(user.getHeadimg() != null) {
				ps.setString(8, user.getHeadimg());
			}
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updateUser(User user) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE user set "
				+ "username = ?,password = ?,sex = ?,realname = ?,email = ?,telephone = ?,idNum = ?";
				if(user.getHeadimg()!=null) {
					sql +=",headimg = ?";
				}
				sql+= " WHERE userId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getSex());
			ps.setString(4, user.getRealname());
			ps.setString(5,  user.getEmail() );
			ps.setString(6, user.getTelephone());
			ps.setString(7, user.getIdNum());
			if(user.getHeadimg()!=null) {
				ps.setString(8, user.getHeadimg());
				ps.setInt(9, user.getUserId());
			}else {
				ps.setInt(8, user.getUserId());
			}
			
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int deleteUser(int userId) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM user WHERE userId = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, userId);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int selectUsers() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM user";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}
}
